package com.bstek.pms.tools.service.impl;

import com.bstek.pms.tools.model.ColumnInfo;
import com.bstek.pms.tools.model.DBProperties;
import com.bstek.pms.tools.model.TableInfo;
import com.bstek.pms.tools.service.DBInfoService;
import com.bstek.pms.tools.service.ExportDocument;
import com.bstek.pms.tools.utils.FileUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileOutputStream;
import java.util.List;
import java.util.Map;

/**
 * @Description 生成单个execl文件
 * @Author zhangkun
 * @CreateDate 2020/8/1
 */
public class ExportDocumentForOneExcel implements ExportDocument {

    //数据库连接参数
    private DBProperties properties;

    //项目参数
    private Map<String,Object> params;


    public ExportDocumentForOneExcel(){

    }

    public ExportDocumentForOneExcel(DBProperties properties, Map<String,Object> params){
        this.properties = properties;
        this.params = params;
    }

    @Override
    public void export() throws Exception {

        //生成文件输出目录
        String exportFilePath = FileUtils.generateExportDirs(params.get("sysName") + "_" + "数据结构变更");

        //计算选中的数据行,从数据库查询表结构，写入到excel中
        List<TableInfo> selectedTabs = DBInfoService.getSelectedTabs(properties, params);

        HSSFWorkbook workbook=new HSSFWorkbook();

        //创建目录页
        createIndexSheet(selectedTabs, workbook);

        //创建数据库表对应页
        createSheets(selectedTabs, workbook);

        //文档输出
        FileOutputStream out = new FileOutputStream(exportFilePath + File.separator + params.get("sysName") + "_" + "数据结构变更文档.xls");
        try{
            workbook.write(out);
        }finally {
            out.close();
        }

    }

    /**
     * 创建目录页
     * @param selectedTabs
     * @param workbook
     */
    private void createIndexSheet(List<TableInfo> selectedTabs, HSSFWorkbook workbook) {
        HSSFSheet sheet = workbook.createSheet("数据库表清单");
        HSSFCellStyle headerStyle = createHeaderStyle(workbook); //创建表头样式
        HSSFCellStyle bodyStyle = createBodyStyle(workbook); //创建表体样式

        String[] indexHeaderCells = new String[] {
                "序号", "表名", "表中文说明"
        };
        //创建工作表表头
        createSheetTableHeader(sheet, headerStyle, indexHeaderCells);

        //创建工作表表体
        createIndexTableBody(selectedTabs, sheet, bodyStyle);
    }

    /**
     * 创建数据库表对应页
     * @param selectedTabs
     * @param workbook
     */
    private void createSheets(List<TableInfo> selectedTabs, HSSFWorkbook workbook) {
        HSSFCellStyle headerStyle = createHeaderStyle(workbook); //创建表头样式
        HSSFCellStyle bodyStyle = createBodyStyle(workbook); //创建表体样式
        //表字段信息，每个表一个sheet页
        for(int i=0; i < selectedTabs.size(); i++) {
            TableInfo tab=selectedTabs.get(i);
            //创建工作页
            HSSFSheet sheet = workbook.createSheet(tab.getTableConment());

            String[] headerCells = new String[] {
                    "表名", "表中文名", "字段索引号", "字段名", "字段中文名", "字段类型", "是否主键", "可否为空", "是否码值", "是否使用", "备注"
            };
            //创建表头
            createSheetTableHeader(sheet, headerStyle, headerCells);

            //创建表体
            createSheetTableBody(sheet, bodyStyle, tab);
        }
    }

    /**
     * 创建表头
     * @param sheet
     * @param headerStyle
     */
    private void createSheetTableHeader(HSSFSheet sheet, HSSFCellStyle headerStyle, String[] headerCells) {
        HSSFRow row = sheet.createRow(0);//设置第一行，从零开始
        row.setHeight((short) (36*20));
        createCell(headerCells, sheet, row, headerStyle);
    }

    /**
     * 创建目录页表格体
     * @param selectedTabs
     * @param sheet
     * @param bodyStyle
     */
    private void createIndexTableBody(List<TableInfo> selectedTabs, HSSFSheet sheet, HSSFCellStyle bodyStyle) {
        for(int k=0; k < selectedTabs.size(); k++){
            HSSFRow row = sheet.createRow(k+1);
            row.setHeight((short)(15*20));
            TableInfo tab=selectedTabs.get(k);
            String[] indexBodyCells = new String[] {
                    (k+1)+"", tab.getTableName(), tab.getTableConment()
            };
            createCell(indexBodyCells, sheet, row, bodyStyle);
        }
    }

    /**
     * 创建数据库表页表体
     * @param sheet
     * @param bodyStyle
     * @param tab
     */
    private void createSheetTableBody(HSSFSheet sheet, HSSFCellStyle bodyStyle, TableInfo tab) {
        List<ColumnInfo> cols=tab.getCols();
        for(int j=0;j<cols.size();j++){
            ColumnInfo col=cols.get(j);
            HSSFRow row = sheet.createRow(1+j);
            row.setHeight((short)(15*20));
            String[] bodyCells = new String[]{
                    tab.getTableName(), tab.getTableConment(), (j+1)+"", col.getName(), col.getLabel(), getFieldType(col), col.getConstraintsType(),
                    ""+col.isNullable(), "N", "Y", ""
            };
            createCell(bodyCells, sheet, row, bodyStyle);
        }
    }


    /**
     * 获取字段类型
     * @param col
     * @return
     */
    private String getFieldType(ColumnInfo col) {
        String fieldType;
        String dataType=col.getDataType();
        int length=col.getLength();//字符长度
        int precision=col.getPrecision();//长度
        int scale=col.getScale();//小数位

        if (dataType.startsWith("NUMBER")) {
            if (scale == 0) {
                fieldType = "Integer";
                //超过Int的范围2147483648的整型是Long
                if (precision >10) {
                    fieldType = "Long";
                }
            }else {
                fieldType = "NUMBER("+precision+","+scale+")";
            }
        } else if (dataType.startsWith("TIMESTAMP")
                || dataType.startsWith("DATE")) {
            fieldType = dataType;
        }else{
            //默认是字段串
            fieldType=dataType+"("+length+")";
        }
        return fieldType;
    }


    /**
     * 批量创建单元格
     * @param values
     * @param sheet
     * @param row
     * @param cellStyle
     */
    private void createCell(String[] values, HSSFSheet sheet, HSSFRow row, HSSFCellStyle cellStyle){
        for (int i = 0; i < values.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(values[i]);
            cell.setCellStyle(cellStyle);
            autoResizeColumnWidth(i, values[i], sheet);
        }
    }

    /**
     * 创建表头样式
     * @param workbook
     * @return
     */
    private HSSFCellStyle createHeaderStyle(HSSFWorkbook workbook){
        HSSFCellStyle cellStyle = workbook.createCellStyle();

        //设置字体
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11); //字体大小
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //设置加粗

        cellStyle.setFont(font); //设置字体
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置内容居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置内容垂直居中
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //设置下边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //设置上边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //设置左边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //设置右边框
        cellStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); //设置背景为50度灰
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //设置填充模式


        return cellStyle;
    }

    /**
     * 创建表体样式
     * @param workbook
     * @return
     */
    private HSSFCellStyle createBodyStyle(HSSFWorkbook workbook){
        HSSFCellStyle cellStyle = workbook.createCellStyle();

        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //设置下边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //设置上边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //设置左边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //设置右边框


        return cellStyle;
    }



    /**
     * 根据单元格内容调节列宽，不用poi方法，一是为了生成效率，二是为了中文字符调整效果更好
     * @param columnIndex
     * @param value
     * @param sheet
     */
    private void autoResizeColumnWidth(int columnIndex, String value, HSSFSheet sheet) {
        int length = 0;
        if(StringUtils.hasText(value))
            length = value.getBytes().length;
        int currentColumnWidth = sheet.getColumnWidth(columnIndex);
        int newColumnWidth = length*350;
        sheet.setColumnWidth(columnIndex,Math.max(currentColumnWidth,newColumnWidth));
    }


}
